# 转换Excel为清单
import pandas as pd
from src.utils import is_digits_and_uppercase
from src.utils import traversal_folder

# order_no_pre = "HDH2508WF"
order_no_pre = "JY25066"
# 报关资料所在文件夹（需要修改）
folder_path = "/Users/wangzhiwei/Desktop/敬业散货报关资料"
# 报关资料序号前缀路径（需要修改）
prePath = "敬业散货报关资料/敬业 散货"
# 载货清单（需要修改）
output_path = "/Users/wangzhiwei/Desktop/载货清单-散伙437.xlsx"

def get_product_info(file_path):
    df = pd.read_excel(file_path, sheet_name='PL')
    product_info = {
        "#No": None,
        "#Shipper": None,
        "#Consignee": None,
        "#RealConsignee": None,
        "#Packages": None,
        "#GoodsName": None,
        "#GoodsNameCn": None,
        "#GWeight": None,
        "#NWeight": None,
        "#Size": None,
        "#Number": None,
    }
    USCI = df.iloc[3, 0]
    if USCI is not None and type(USCI) == str:
        product_info["#Shipper"] = df.iloc[0, 0] + "\n" + df.iloc[1, 0] + "\n" + USCI
    else:
        product_info["#Shipper"] = df.iloc[0, 0] + "\n" + df.iloc[1, 0]
    for col in range(len(df.columns)):
        for i in range(len(df)):
            value = df.iloc[i, col]
            if type(value) == str and "实际收货人" in value:
                product_info["#RealConsignee"] = ""
                single = False
                if "三方贸易，实际收货人如下，请按此订舱：" in value:
                    remainContent = value[len("三方贸易，实际收货人如下，请按此订舱："):]
                    if len(remainContent) >= 1:
                        single = True
                        product_info["#RealConsignee"] += value[len("三方贸易，实际收货人如下，请按此订舱："):]
                if not single:
                    real_consignee_buffer = ""
                    step = 1
                    isEnd = False
                    real_consignee = df.iloc[i + step, col]
                    while ((real_consignee is not None and type(real_consignee) is str) or not isEnd) :
                        if real_consignee is not None and type(real_consignee) is str:
                            if real_consignee_buffer == "":
                                real_consignee_buffer += real_consignee
                            else:
                                real_consignee_buffer += ("\n" + real_consignee)
                            isEnd = True
                        step+=1
                        real_consignee = df.iloc[i + step, col]
                    product_info["#RealConsignee"] += real_consignee_buffer
            if type(value) == str and "COMPANY" in value and col == 0:
                product_info["#Consignee"] = df.iloc[i, col + 1] + "\n" + df.iloc[i + 1, col + 1]
            if "TOTAL" == value:
                product_info["#Packages"] = f"{df.iloc[i, col + 2]}"
                product_info["#GWeight"] = f"{df.iloc[i, col + 4]}"
                product_info["#NWeight"] = f"{df.iloc[i, col + 5]}"
            if "NAME" == value:
                right_name = df.iloc[i, col + 1]
                if "QUANTITY" == right_name:
                    product_name_list = None
                    step = 2
                    product_name = df.iloc[i + step, col]
                    product_name_map = {}
                    while product_name is not None and type(product_name) is str :
                        if product_name_map.get(product_name) is None:
                            product_name_map.setdefault(product_name, 1)
                            if product_name_list is None:
                                product_name_list = product_name
                            else:
                                product_name_list += ("\n" + product_name)
                        step+=1
                        product_name = df.iloc[i + step, col]
                    product_info["#GoodsNameCn"] = product_name_list
            if "NET" == value:
                product_info["#Size"] = df.iloc[i - 1, col + 1]
                product_name_list = None
                step = 1
                product_name = df.iloc[i + step, col + 1]
                product_name_map = {}
                while product_name is not None and type(product_name) is str :
                    if product_name_map.get(product_name) is None:
                        product_name_map.setdefault(product_name, 1)
                        if product_name_list is None:
                            product_name_list = product_name
                        else:
                            product_name_list += ("\n" + product_name)
                    step+=1
                    product_name = df.iloc[i + step, col + 1]
                product_info["#GoodsName"] = product_name_list
    return product_info
# 调用函数
files = traversal_folder(folder_path)
print("读取文件列表完成，开始打印...")
order_no_list = []
product_name_list = []
product_name_cn_list = []
pks_list = []
gross_list = []
net_list = []
size_list = []
shipper_list = []
consignee_list = []
for file in files:
    print("数据抽取中：" + file)
    extNameIndex = file.find('.xlsx')
    if extNameIndex >= 0:
        keywordIndex = file.find(prePath)
        number = ''
        i = keywordIndex + len(prePath)
        numberChar = file[i]
        while is_digits_and_uppercase(numberChar):
            number += numberChar
            i += 1
            numberChar = file[i]
        product_info = get_product_info(file)
        order_no_list.append(f"{order_no_pre}{number}")
        product_name_list.append(product_info["#GoodsName"])
        product_name_cn_list.append(product_info["#GoodsNameCn"])
        pks_list.append(product_info["#Packages"])
        gross_list.append(product_info["#GWeight"])
        shipper_list.append(product_info["#Shipper"])
        net_list.append(product_info["#NWeight"])
        size_list.append(product_info["#Size"])
        if product_info["#RealConsignee"] is not None:
            consignee_list.append(product_info["#RealConsignee"])
        else:
            consignee_list.append(product_info["#Consignee"])
    else:
        print("非表格文件，跳过")
print("数据导出中...")
result_data = {
    "Order No": order_no_list,
    "Product Name": product_name_list,
    "Product Name Cn": product_name_cn_list,
    "Pks": pks_list,
    "Gross": gross_list,
    "Net": net_list,
    "Shipper": shipper_list,
    "Consignee": consignee_list,
    "Size": size_list,
}
df = pd.DataFrame(result_data)
df.to_excel(output_path, index=False)

